# _*_ coding: utf-8 _*_
from odoo import models, fields, api, tools


class StockMonthBalanceReport(models.Model):
    _name = "stock.month.balance.report"
    _auto = False
    _description = u"进销存汇总表"

    month = fields.Many2one("resource.calendar", string=u"月份")
    prod_lot_id = fields.Many2one('stock.production.lot', u'批次/序列号')
    product_id = fields.Many2one('product.product', u'产品', readonly=True, related='prod_lot_id.product_id')
    product_uom_id = fields.Many2one('product.uom', u'单位', readonly=True, related="prod_lot_id.product_uom_id")
    location_id = fields.Many2one('stock.location', u'库位')
    product_qty_in = fields.Float(u'入数量')
    product_qty_out = fields.Float(u'出数量')
    product_qty_start = fields.Float(string=u"期初数量")
    product_qty_end = fields.Float(u'期末结存')

    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_month_balance_report')
        self._cr.execute("""
            CREATE VIEW stock_month_balance_report AS (
                select
                    line.id,
                    line.month,
                    line.prod_lot_id,
                    line.location_id,
                    line.product_qty_in,
                    line.product_qty_out,
                    balance.product_qty_end as product_qty_start,
                    coalesce(balance.product_qty_end,0) + coalesce(line.product_qty_in,0) - coalesce(line.product_qty_out,0) as product_qty_end 
                from stock_inout_line as line 
					left join stock_location as location on line.location_id = location.id
                    left join resource_calendar as res on line.month = res.id
                    left join stock_month_balance as balance 
                    on res.previous_month_id = balance.month and 
                    line.product_id = balance.product_id and 
                    line.location_id = balance.location_id and 
                    line.prod_lot_id = balance.prod_lot_id
				where location.usage = 'internal' and not (line.product_qty_in = 0 and line.product_qty_end=0 and coalesce(product_qty_start,0)=0)
                union 
                select
                    balance.id,
                    res.next_month_id as month,
                    balance.prod_lot_id,
                    balance.location_id,
                    0 as product_qty_in,
                    0 as product_qty_out,
                    balance.product_qty_end as product_qty_start,
                    balance.product_qty_end 
                from stock_month_balance as balance
				left join stock_location as location on balance.location_id = location.id
                left join resource_calendar as res on balance.month = res.id
                where not exists (select id from stock_inout_line as line 
                where res.next_month_id = line.month and 
                    line.product_id = balance.product_id and 
                    line.location_id = balance.location_id and 
                    line.prod_lot_id = balance.prod_lot_id 
                ) and location.usage = 'internal' and balance.product_qty_end > 0
            )""")
